/**
 * @author Fang Lu, fanglupku@gmail.com
 * 2012-2-9
 */
package com.autotag.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.autotag.entity.Service;

import fanglu.util.StringDealer;

public class ServiceDao {
	private static boolean checkExist(Connection conn,
			Service service) {
		try {
			Statement stm = conn.createStatement();
			ResultSet rs = stm
					.executeQuery("select id from service where ref_seekda = '"
							+ service.getRefSeekda() + "'");
			if (rs.next()) {
				return true;// the user already exists
			} else {
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return true;
		}
	}
	
	public static boolean insertService(Connection conn,
			Service service) {
		if (service.getRefSeekda() == null) {
			return false;
		}
		if (checkExist(conn, service)) {
			System.err.println("Service:" + service.getRefSeekda()
					+ " already exists in database");
			return false;// the user already exists
		}
		try {
			if (service.getId() < 0) {
				PreparedStatement pst = conn
						.prepareStatement("insert into service (name, ref_seekda, provider_name, wsdl, status) values (?,?,?,?,?)");
				pst.setString(1, StringDealer.getStringForMaxLength(Service.MAX_LENGTH_NAME,
						service.getName()));
				pst.setString(2, StringDealer.getStringForMaxLength(Service.MAX_LENGTH_REF_SEEKDA,
						service.getRefSeekda()));
				pst.setString(3, StringDealer.getStringForMaxLength(Service.MAX_LENGTH_PROVIDER_NAME,
						service.getProviderName()));
				pst.setString(4, StringDealer.getStringForMaxLength(Service.MAX_LENGTH_WSDL,
						service.getWSDL()));
				pst.setInt(5, service.getStatus());
				pst.executeUpdate();
				pst.close();
			} else {
				PreparedStatement pst = conn
						.prepareStatement("insert into service (id, name, ref_seekda, provider_name, wsdl, status) values (?,?,?,?,?,?)");
				pst.setInt(1, service.getId());
				pst.setString(2, StringDealer.getStringForMaxLength(Service.MAX_LENGTH_NAME,
						service.getName()));
				pst.setString(3, StringDealer.getStringForMaxLength(Service.MAX_LENGTH_REF_SEEKDA,
						service.getRefSeekda()));
				pst.setString(4, StringDealer.getStringForMaxLength(Service.MAX_LENGTH_PROVIDER_NAME,
						service.getProviderName()));
				pst.setString(5, StringDealer.getStringForMaxLength(Service.MAX_LENGTH_WSDL,
						service.getWSDL()));
				pst.setInt(6, service.getStatus());
				pst.executeUpdate();
				pst.close();
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public static boolean updateService(Connection conn,
			Service serviceEntity) {
		if (serviceEntity.getRefSeekda() == null
				&& serviceEntity.getId() <= 0) {
			return false;
		}
		try {
			String sqlString = "update service ";
			if (serviceEntity.getId() > 0) {
				sqlString += "set id=?";
			} else {
				sqlString += "set ref_seekda=?";
			}
			if (serviceEntity.getName() != null) {
				sqlString += ",name=?";
			}
			if (serviceEntity.getProviderName() != null) {
				sqlString += ",provider_name=?";
			}
			if (serviceEntity.getWSDL() != null) {
				sqlString += ",wsdl=?";
			}
			if (serviceEntity.getStatus() > 0) {
				sqlString += ",status=?";
			}
			if (serviceEntity.getRefSeekda() != null){
				sqlString += ",ref_seekda=?";
			}
			if (serviceEntity.getId() > 0) {
				sqlString += " where id=" + serviceEntity.getId();
			} else {
				sqlString += " where ref_seekda='" + serviceEntity.getRefSeekda()
						+ "'";
			}
			PreparedStatement pst = conn.prepareStatement(sqlString);
			int index_para = 1;
			if (serviceEntity.getId() > 0) {
				pst.setInt(index_para, serviceEntity.getId());
			} else {
				pst.setString(index_para, StringDealer.getStringForMaxLength(
						Service.MAX_LENGTH_REF_SEEKDA, serviceEntity.getRefSeekda()));
			}
			++index_para;
			if (serviceEntity.getName() != null) {
				pst.setString(index_para, StringDealer.getStringForMaxLength(
						Service.MAX_LENGTH_NAME, serviceEntity.getName()));
				++index_para;
			}
			if (serviceEntity.getProviderName() != null) {
				pst.setString(index_para, StringDealer.getStringForMaxLength(
						Service.MAX_LENGTH_PROVIDER_NAME, serviceEntity.getProviderName()));
				++index_para;
			}
			if (serviceEntity.getWSDL() != null) {
				pst.setString(index_para, StringDealer.getStringForMaxLength(
						Service.MAX_LENGTH_WSDL, serviceEntity.getWSDL()));
				++index_para;
			}
			if (serviceEntity.getStatus() > 0) {
				pst.setInt(index_para, serviceEntity.getStatus());
				++index_para;
			}
			if (serviceEntity.getRefSeekda() != null) {
				pst.setString(index_para, StringDealer.getStringForMaxLength(
						Service.MAX_LENGTH_REF_SEEKDA, serviceEntity.getRefSeekda()));
				++index_para;
			}

			pst.executeUpdate();
			pst.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public static List<Service> getAllService(Connection conn){
		List<Service> resultList = new ArrayList<Service>();
		String sqlString = "select id,name,ref_seekda,provider_name,wsdl,status from service";
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet resultSet = null;
			resultSet = stmt.executeQuery(sqlString);
			while (resultSet.next()) {
				int thisId = resultSet.getInt(1);
				String thisName = resultSet.getString(2);
				String thisRef = resultSet.getString(3);
				String thisProviderName = resultSet.getString(4);
				String thisWSDL = resultSet.getString(5);
				int thisStatus = resultSet.getInt(6);
				Service thisEntity = new Service(thisId, thisName, thisRef, thisProviderName, thisWSDL, thisStatus, null);
				resultList.add(thisEntity);
			}
			resultSet.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultList;
	}
}
